<?php

	
function connect()
{
	// DB connection info
	$host = "tcp:djnrgfij6m.database.windows.net,1433"; 
	$user = "gexpress";
	$pwd = "Groceryexpress2014";
	$db = "Grocery-Express";
	try{
		$conn = new PDO( "sqlsrv:Server= $host ; Database = $db ", $user, $pwd);
		$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
	}
	catch(Exception $e){
		die(print_r($e));
	}
	return $conn;
}

function markItemComplete($item_id)
{
	$conn = connect();
	$sql = "UPDATE items SET is_complete = 1 WHERE id = ?";
	$stmt = $conn->prepare($sql);
	$stmt->bindValue(1, $item_id);
	$stmt->execute();
}

function getProductsInCart($id)
{
    $conn = connect();
    $query = "SELECT productNumber, productName, price FROM products WHERE productNumber=?";
    $stmt = $conn->prepare( $query );
    $stmt->bindValue(1,$id);
    $stmt->execute();
    return $stmt->fetch(PDO::FETCH_ASSOC);
}

function getBeverages()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'beverages'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getCannedGoods()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'canned goods'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getDairy()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'dairy'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getInstantGoods()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'instant goods'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getSnacks()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'snacks'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getForBody()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'body care'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getForHair()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'hair care'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getToiletries()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'toiletries'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getForLaundry()
{
	$conn = connect();
    $query = "SELECT productNumber, productName, price, images FROM products WHERE category = 'laundry'";
    $stmt = $conn->query( $query );
    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function checkRegisteredUser($username,$password)
{
    $conn = connect();
    //$query = mysql_query("select * from login where password='$password' AND username='$username'", $connection);
    /*
        $sql = "SELECT count(*) FROM `table` WHERE foo = bar"; 
        $result = $con->prepare($sql); 
        $result->execute(); 
        $number_of_rows = $result->fetchColumn(); 
    */
    $query = "SELECT count(*) FROM registeredusers WHERE username=? AND password =?";
    $stmt = $conn->prepare($query);
    $stmt->bindValue(1, $username);
    $stmt->bindValue(2, $password);
    $stmt->execute();
    return $stmt->fetchColumn(); 
}

function addItem($userNumber,$username, $password, $emailadd,$mobileno)
{
	$conn = connect();
	$sql = "INSERT INTO registeredusers (userNumber, username, password, emailadd,mobileno) VALUES (?, ?, ?, ?,?)";
	$stmt = $conn->prepare($sql);
    $stmt->bindValue(1, $userNumber);
	$stmt->bindValue(2, $username);
	$stmt->bindValue(3, $password);
	$stmt->bindValue(4, $emailadd);
    $stmt->bindValue(5, $mobileno);
	$stmt->execute();

}

function deleteItem($item_id)
{
	$conn = connect();
	$sql = "DELETE FROM items WHERE id = ?";
	$stmt = $conn->prepare($sql);
	$stmt->bindValue(1, $item_id);
	$stmt->execute();
}

?>